﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using Workbench.Models;
using Workbench.Shared;

namespace Workbench.Persistance
{
    public class ConcordancerRepository : RepositoryBase
    {
        public ConcordancerRepository(Func<SQLiteConnection> connectionProvider) : base(connectionProvider) { }

        private class _WordModel
        {
            public string WordForm;
            public string BaseForm;

            public static bool AreEqual(SuggestionType suggestionType, _WordModel word, string content)
            {
                if(suggestionType == SuggestionType.BaseForm)
                {
                    return word.BaseForm == content;
                }
                if (suggestionType == SuggestionType.WordForm)
                {
                    return word.WordForm == content;
                }
                return false;
            }
        }

        public List<WordWithNearWordsModel> GetNearWords(IEnumerable<ProjectFileModel> files, WordSuggestionModel word, SuggestionType suggestionType)
        {
            string cmdText = string.Empty;
            switch (suggestionType)
            {
                case SuggestionType.BaseForm:
                    cmdText = string.Format(
@"SELECT w.SentenceId, uw.Content as WordForm, bf.Content as BaseForm
FROM Words w
INNER JOIN UniqueWords AS uw ON uw.RowId = w.UniqueWordId
LEFT JOIN BaseForms AS bf ON bf.RowId = w.BaseFormId
INNER JOIN Sentences AS s ON s.RowId = w.SentenceId
INNER JOIN Paragraphs AS p ON p.RowId = s.ParagraphId
WHERE p.FileId IN ({1}) AND s.RowId IN (SELECT SentenceId FROM Words WHERE BaseFormId = {0})
ORDER BY w.RowId", word.RowId, string.Join(",", files.Select(x => x.RowId)));
                    break;
                case SuggestionType.WordForm:
                    cmdText = string.Format(
@"SELECT w.SentenceId, uw.Content as WordForm, bf.Content as BaseForm
FROM Words w
INNER JOIN UniqueWords AS uw ON w.UniqueWordId = uw.RowId
LEFT JOIN BaseForms AS bf ON bf.RowId = w.BaseFormId
INNER JOIN Sentences AS s ON s.RowId = w.SentenceId
INNER JOIN Paragraphs AS p ON p.RowId = s.ParagraphId
WHERE p.FileId IN ({1}) AND SentenceId IN (SELECT SentenceId FROM Words WHERE UniqueWordId = {0})
ORDER BY w.RowId", word.RowId, string.Join(",", files.Select(x => x.RowId)));
                    break;
                default:
                    throw new NotSupportedException(string.Format("SuggestionType: {0} is not supported.", suggestionType.ToString()));
            }

            var sentences = new Dictionary<long, List<_WordModel>>();
            using (var reader = DbUtility.ExecuteReader(connectionProvider(), cmdText))
            {
                while (reader.Read())
                {
                    if (reader.IsDBNull(0)) continue;
                    long sentenceId = reader.GetInt64(0);
                    if (reader.IsDBNull(1)) continue;
                    var model = new _WordModel();
                    model.WordForm = reader.GetString(1);
                    if(suggestionType == SuggestionType.BaseForm)
                    {
                        if(!reader.IsDBNull(2)) model.BaseForm= reader.GetString(2);
                    }

                    sentences.AddToValueList(sentenceId, model);
                }
            }

            var result = new List<WordWithNearWordsModel>();
            foreach (var sentence in sentences)
            {
                var sentenceId = sentence.Key;
                var wordList = sentence.Value;
                for (int i = 0; i < wordList.Count; i++)
                {
                    var wordi = wordList[i];
                    if (_WordModel.AreEqual(suggestionType,  wordi, word.Content))
                    {
                        var model = new WordWithNearWordsModel() { Word = wordi.WordForm };
                        model.SentenceId = sentenceId;
                        for (int j = 1; j <= 5; j++)
                        {
                            if (i - j < 0) break;
                            model.WordsToTheLeft.Add(wordList[i - j].WordForm);
                        }
                        for (int j = 1; j <= 5; j++)
                        {
                            if (i + j >= wordList.Count) break;
                            model.WordsToTheRight.Add(wordList[i + j].WordForm);
                        }
                        result.Add(model);
                    }
                }
            }

            return result;
        }

        public List<WordSuggestionModel> GetWordFormSuggestions(IEnumerable<ProjectFileModel> files, string currentSearchContent, SuggestionType suggestionType)
        {
            var result = new List<WordSuggestionModel>();
            var parameters = new Dictionary<string, object>();
            parameters.Add("@contentStart", string.Format("{0}%", currentSearchContent));

            string cmdText;
            switch (suggestionType)
            {
                case SuggestionType.BaseForm:
                    cmdText =
string.Format(@"SELECT DISTINCT bf.RowId, bf.Content 
FROM Words AS w
INNER JOIN BaseForms AS bf ON bf.RowId = w.BaseFormId
INNER JOIN Sentences AS s ON s.RowId = w.SentenceId
INNER JOIN Paragraphs AS p ON p.RowId = s.ParagraphId
WHERE p.FileId IN ({0}) AND bf.Content LIKE @contentStart
ORDER BY bf.Content
LIMIT 100", string.Join(",", files.Select(x => x.RowId)));
                    break;
                case SuggestionType.WordForm:
                    cmdText =
string.Format(@"SELECT DISTINCT uw.RowId, uw.Content 
FROM Words AS w
INNER JOIN UniqueWords AS uw ON uw.RowId = w.UniqueWordId
INNER JOIN Sentences AS s ON s.RowId = w.SentenceId
INNER JOIN Paragraphs AS p ON p.RowId = s.ParagraphId
WHERE p.FileId IN ({0}) AND uw.Content LIKE @contentStart
ORDER BY uw.Content
LIMIT 100", string.Join(",", files.Select(x => x.RowId)));
                    break;
                default:
                    throw new NotSupportedException(string.Format("SuggestionType: {0} is not supported.", suggestionType.ToString()));
            }

            using (var reader = DbUtility.ExecuteReader(connectionProvider(), cmdText, parameters))
            {
                while (reader.Read())
                {
                    var model = new WordSuggestionModel();
                    model.SuggestionType = suggestionType;
                    if (!reader.IsDBNull(0))
                    {
                        model.RowId = reader.GetInt64(0);
                        if (!reader.IsDBNull(1))
                        {
                            model.Content = reader.GetString(1);
                            result.Add(model);
                        }
                    }
                }
            }
            return result;
        }
    }
}
